﻿/**  版本信息模板在安装目录下，可自行修改。
* Sys_User.cs
*
* 功 能： N/A
* 类 名： Sys_User
*
* Ver    变更日期             负责人  变更内容
* ───────────────────────────────────
* V0.01  2021/3/29 10:15:43   N/A    初版
*
* Copyright (c) 2012 Maticsoft Corporation. All rights reserved.
*┌──────────────────────────────────┐
*│　此技术信息为本公司机密信息，未经本公司书面同意禁止向第三方披露．　│
*│　版权所有：动软卓越（北京）科技有限公司　　　　　　　　　　　　　　│
*└──────────────────────────────────┘
*/
using System;
using System.Data;
using System.Text;
using System.Data.SqlClient;
using DBUtility;//Please add references
using System.Collections.Generic;
using Common;

namespace DeliveryManage.DAL
{
	/// <summary>
	/// 数据访问类:Sys_User
	/// </summary>
	public partial class Sys_User
	{
		public Sys_User()
		{}
		#region  BasicMethod

		/// <summary>
		/// 得到最大ID
		/// </summary>
		public int GetMaxId()
		{
		return DbHelperSQL.GetMaxID("id", "Sys_User"); 
		}

		/// <summary>
		/// 是否存在该记录
		/// </summary>
		public bool Exists(int id)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("select count(1) from Sys_User");
			strSql.Append(" where id=@id");
			SqlParameter[] parameters = {
					new SqlParameter("@id", SqlDbType.Int,4)
			};
			parameters[0].Value = id;

			return DbHelperSQL.Exists(strSql.ToString(),parameters);
		}


        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int Add(DeliveryManage.Model.Sys_User model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into Sys_User(");
            strSql.Append("Name,Pwd,MaxUserCount,ChildCount,TopUser,Status,LastTask,Nickname,Telephone,RoleType,Email,RemandVal,IsDisabledCall,UnUploadCount,IsBasicSalary)");
            strSql.Append(" values (");
            strSql.Append("@Name,@Pwd,@MaxUserCount,@ChildCount,@TopUser,@Status,@LastTask,@Nickname,@Telephone,@RoleType,@Email,@RemandVal,@IsDisabledCall,@UnUploadCount,@IsBasicSalary)");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
                    new SqlParameter("@Name", SqlDbType.NVarChar,16),
                    new SqlParameter("@Pwd", SqlDbType.NVarChar,32),
                    new SqlParameter("@MaxUserCount", SqlDbType.Int,4),
                    new SqlParameter("@ChildCount", SqlDbType.Int,4),
                    new SqlParameter("@TopUser", SqlDbType.Int,4),
                    new SqlParameter("@Status", SqlDbType.Bit,1),
                    new SqlParameter("@LastTask", SqlDbType.Int,4),
                    new SqlParameter("@Nickname", SqlDbType.VarChar,20),
                    new SqlParameter("@Telephone", SqlDbType.VarChar,20),
                    new SqlParameter("@RoleType", SqlDbType.Int,4),
                    new SqlParameter("@Email", SqlDbType.NVarChar,30),
                    new SqlParameter("@RemandVal",SqlDbType.TinyInt,4),
                    new SqlParameter("@IsDisabledCall",SqlDbType.Bit,1),
                    new SqlParameter("@UnUploadCount",SqlDbType.TinyInt,4),
                    new SqlParameter("@IsBasicSalary",SqlDbType.Bit,1),
            };
            parameters[0].Value = model.Name;
            parameters[1].Value = model.Pwd;
            parameters[2].Value = model.MaxUserCount;
            parameters[3].Value = model.ChildCount;
            parameters[4].Value = model.TopUser;
            parameters[5].Value = model.Status;
            parameters[6].Value = model.LastTask;
            parameters[7].Value = model.Nickname;
            parameters[8].Value = model.Telephone;
            parameters[9].Value = model.RoleType;
            parameters[10].Value = model.Email;
            parameters[11].Value = model.RemandVal;
            parameters[12].Value = model.IsDisabledCall;
            parameters[13].Value = model.UnUploadCount;
            parameters[144].Value = model.IsBasicSalary;

            object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }
        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(DeliveryManage.Model.Sys_User model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update Sys_User set ");
            strSql.Append("Name=@Name,");
            strSql.Append("Pwd=@Pwd,");
            strSql.Append("MaxUserCount=@MaxUserCount,");
            strSql.Append("ChildCount=@ChildCount,");
            strSql.Append("TopUser=@TopUser,");
            strSql.Append("Status=@Status,");
            strSql.Append("LastTask=@LastTask,");
            strSql.Append("Nickname=@Nickname,");
            strSql.Append("Telephone=@Telephone,");
            strSql.Append("RoleType=@RoleType,");
            strSql.Append("Email=@Email,");
            strSql.Append("RemandVal=@RemandVal,");
            strSql.Append("IsDisabledCall=@IsDisabledCall,");
            strSql.Append("UnUploadCount=@UnUploadCount,");
            strSql.Append("IsBasicSalary=@IsBasicSalary");
            strSql.Append(" where Id=@Id");
            SqlParameter[] parameters = {
                    new SqlParameter("@Name", SqlDbType.NVarChar,16),
                    new SqlParameter("@Pwd", SqlDbType.NVarChar,32),
                    new SqlParameter("@MaxUserCount", SqlDbType.Int,4),
                    new SqlParameter("@ChildCount", SqlDbType.Int,4),
                    new SqlParameter("@TopUser", SqlDbType.Int,4),
                    new SqlParameter("@Status", SqlDbType.Bit,1),
                    new SqlParameter("@LastTask", SqlDbType.Int,4),
                    new SqlParameter("@Nickname", SqlDbType.VarChar,20),
                    new SqlParameter("@Telephone", SqlDbType.VarChar,20),
                    new SqlParameter("@RoleType", SqlDbType.Int,4),
                    new SqlParameter("@Email", SqlDbType.VarChar,30),
                    new SqlParameter("@RemandVal", SqlDbType.TinyInt,4),
                    new SqlParameter("@IsDisabledCall",SqlDbType.Bit,1),
                    new SqlParameter("@UnUploadCount",SqlDbType.TinyInt,4),
                    new SqlParameter("@IsBasicSalary",SqlDbType.Bit,1),
                    new SqlParameter("@Id", SqlDbType.Int,4)};
            parameters[0].Value = model.Name;
            parameters[1].Value = model.Pwd;
            parameters[2].Value = model.MaxUserCount;
            parameters[3].Value = model.ChildCount;
            parameters[4].Value = model.TopUser;
            parameters[5].Value = model.Status;
            parameters[6].Value = model.LastTask;
            parameters[7].Value = model.Nickname;
            parameters[8].Value = model.Telephone;
            parameters[9].Value = model.RoleType;
            parameters[10].Value = model.Email;
            parameters[11].Value = model.RemandVal;
            parameters[12].Value = model.IsDisabledCall;
            parameters[13].Value = model.UnUploadCount;
            parameters[14].Value = model.IsBasicSalary;
            parameters[15].Value = model.Id;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }


        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(int id)
		{
			
			StringBuilder strSql=new StringBuilder();
			strSql.Append("delete from Sys_User ");
			strSql.Append(" where id=@id");
			SqlParameter[] parameters = {
					new SqlParameter("@id", SqlDbType.Int,4)
			};
			parameters[0].Value = id;

			int rows=DbHelperSQL.ExecuteSql(strSql.ToString(),parameters);
			if (rows > 0)
			{
				return true;
			}
			else
			{
				return false;
			}
		}
		/// <summary>
		/// 批量删除数据
		/// </summary>
		public bool DeleteList(string idlist )
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("delete from Sys_User ");
			strSql.Append(" where id in ("+idlist + ")  ");
			int rows=DbHelperSQL.ExecuteSql(strSql.ToString());
			if (rows > 0)
			{
				return true;
			}
			else
			{
				return false;
			}
		}

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public DeliveryManage.Model.Sys_User GetModel(int Id)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 Id,Name,Pwd,MaxUserCount,ChildCount,TopUser,Status,LastTask,Nickname,Telephone,RoleType,Email,RemandVal,IsDisabledCall,UnUploadCount,IsBasicSalary from Sys_User ");
            strSql.Append(" where Id=@Id");
            SqlParameter[] parameters = {
                    new SqlParameter("@Id", SqlDbType.Int,4)
            };
            parameters[0].Value = Id;

            DeliveryManage.Model.Sys_User model = new DeliveryManage.Model.Sys_User();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                return DataRowToModel(ds.Tables[0].Rows[0]);
            }
            else
            {
                return null;
            }
        }


        /// <summary>
		/// 得到一个对象实体
		/// </summary>
		public DeliveryManage.Model.Sys_User DataRowToModel(DataRow row)
        {
            DeliveryManage.Model.Sys_User model = new DeliveryManage.Model.Sys_User();
            if (row != null)
            {
                if (row["Id"] != null && row["Id"].ToString() != "")
                {
                    model.Id = int.Parse(row["Id"].ToString());
                }
                if (row["Name"] != null)
                {
                    model.Name = row["Name"].ToString();
                }
                if (row["Pwd"] != null)
                {
                    model.Pwd = row["Pwd"].ToString();
                }
                if (row["MaxUserCount"] != null && row["MaxUserCount"].ToString() != "")
                {
                    model.MaxUserCount = int.Parse(row["MaxUserCount"].ToString());
                }
                if (row["ChildCount"] != null && row["ChildCount"].ToString() != "")
                {
                    model.ChildCount = int.Parse(row["ChildCount"].ToString());
                }
                if (row["TopUser"] != null && row["TopUser"].ToString() != "")
                {
                    model.TopUser = int.Parse(row["TopUser"].ToString());
                }
                if (row["Status"] != null && row["Status"].ToString() != "")
                {
                    if ((row["Status"].ToString() == "1") || (row["Status"].ToString().ToLower() == "true"))
                    {
                        model.Status = true;
                    }
                    else
                    {
                        model.Status = false;
                    }
                }
                if (row["LastTask"] != null && row["LastTask"].ToString() != "")
                {
                    model.LastTask = int.Parse(row["LastTask"].ToString());
                }
                if (row["Nickname"] != null)
                {
                    model.Nickname = row["Nickname"].ToString();
                }
                if (row["RoleType"] != null && row["RoleType"].ToString() != "")
                {
                    model.RoleType = int.Parse(row["RoleType"].ToString());
                }
                if (row["Telephone"] != null)
                {
                    model.Telephone = row["Telephone"].ToString();
                }
                if (row["Email"] != null)
                {
                    model.Email = row["Email"].ToString();
                }
                if (row["RemandVal"] != null && row["RemandVal"].ToString() != "")
                {
                    model.RemandVal = int.Parse(row["RemandVal"].ToString());
                }
                if (row["IsDisabledCall"] != null && row["IsDisabledCall"].ToString() != "")
                {
                    model.IsDisabledCall = bool.Parse(row["IsDisabledCall"].ToString());
                }
                if (row["UnUploadCount"] != null && row["UnUploadCount"].ToString() != "")
                {
                    model.UnUploadCount = int.Parse(row["UnUploadCount"].ToString());
                }
                if (row["IsBasicSalary"] != null && row["IsBasicSalary"].ToString() != "")
                {
                    model.IsBasicSalary = bool.Parse(row["IsBasicSalary"].ToString());
                }
            }
            return model;
        }

        /// <summary>
		/// 获得数据列表
		/// </summary>
		public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select Id,Name,Pwd,MaxUserCount,ChildCount,TopUser,Status,LastTask,Nickname,Telephone,RoleType,Email,RemandVal,IsDisabledCall,UnUploadCount,IsBasicSalary ");
            strSql.Append(" FROM Sys_User ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
		/// 获得前几行数据
		/// </summary>
		public DataSet GetList(int Top, string strWhere, string filedOrder)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            if (Top > 0)
            {
                strSql.Append(" top " + Top.ToString());
            }
            strSql.Append(" Id,Name,Pwd,MaxUserCount,ChildCount,TopUser,Status,LastTask,Nickname,Telephone,RoleType,Email,RemandVal,IsDisabledCall,UnUploadCount,IsBasicSalary ");
            strSql.Append(" FROM Sys_User ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(" order by " + filedOrder);
            return DbHelperSQL.Query(strSql.ToString());
        }
        /// <summary>
        /// 获取记录总数
        /// </summary>
        public int GetRecordCount(string strWhere)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("select count(1) FROM Sys_User ");
			if(strWhere.Trim()!="")
			{
				strSql.Append(" where "+strWhere);
			}
			object obj = DbHelperSQL.GetSingle(strSql.ToString());
			if (obj == null)
			{
				return 0;
			}
			else
			{
				return Convert.ToInt32(obj);
			}
		}
		/// <summary>
		/// 分页获取数据列表
		/// </summary>
		public DataSet GetListByPage(string strWhere, string orderby, int startIndex, int endIndex)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("SELECT * FROM ( ");
			strSql.Append(" SELECT ROW_NUMBER() OVER (");
			if (!string.IsNullOrEmpty(orderby.Trim()))
			{
				strSql.Append("order by T." + orderby );
			}
			else
			{
				strSql.Append("order by T.id desc");
			}
			strSql.Append(")AS Row, T.*  from Sys_User T ");
			if (!string.IsNullOrEmpty(strWhere.Trim()))
			{
				strSql.Append(" WHERE " + strWhere);
			}
			strSql.Append(" ) TT");
			strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex);
			return DbHelperSQL.Query(strSql.ToString());
		}
        #endregion  BasicMethod
        #region  ExtensionMethod
        /// <summary>
        /// 查询所有下级
        /// </summary>
        public string GetLowerUIds(int TopUser)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(";WITH Tree ");
            strSql.Append("AS( ");
            strSql.Append("SELECT Id FROM [Sys_User] WHERE TopUser= " + TopUser);
            strSql.Append("UNION ALL ");
            strSql.Append("SELECT tab.Id  FROM [Sys_User] tab inner JOIN Tree ON   tab.TopUser =Tree.id");
            strSql.Append(") ");
            strSql.Append("SELECT Id FROM Tree ");

            string UIds = "";
            DataSet ds = DbHelperSQL.Query(strSql.ToString());
            if (ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow item in ds.Tables[0].Rows)
                {
                    UIds += item["Id"] + ",";
                }
            }
            return UIds.TrimEnd(',');
        }

        /// <summary>
        /// 根据用户名密码返回一个实体
        /// </summary>
        public Model.Sys_User GetModel(string user_name, string password)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select top 1 * from Sys_User ");
            strSql.Append(" where name=@user_name and pwd=@password and status=1");
            SqlParameter[] parameters = {
                    new SqlParameter("@user_name", SqlDbType.NVarChar,30),
                    new SqlParameter("@password", SqlDbType.NVarChar,50)};
            parameters[0].Value = user_name;
            parameters[1].Value = password;

            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                return DataRowToModel(ds.Tables[0].Rows[0]);
            }
            return null;
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public Model.Sys_User GetModelByName(string username)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select top 1 * from Sys_User ");
            strSql.Append(" where name=@name" +
                "");
            SqlParameter[] parameters = {
                    new SqlParameter("@name", SqlDbType.NVarChar,30)          };
            parameters[0].Value = username;

            Model.Sys_User model = new Model.Sys_User();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                return DataRowToModel(ds.Tables[0].Rows[0]);
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 获得查询分页数据
        /// </summary>
        public List<DeliveryManage.Model.Sys_User> GetQueryList(int pageSize, int pageIndex, string strWhere, string filedOrder, out int recordCount)
        {
            List<DeliveryManage.Model.Sys_User> list = new List<DeliveryManage.Model.Sys_User>();
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * from (");
            strSql.Append("select * FROM Sys_User");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(") dual");
            recordCount = Convert.ToInt32(DbHelperSQL.GetSingle(PagingHelper.CreateCountingSql(strSql.ToString())));
            DataSet ds = DbHelperSQL.Query(PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, strSql.ToString(), filedOrder));
            if (ds != null && ds.Tables[0] != null)
            {
                foreach (DataRow item in ds.Tables[0].Rows)
                {
                    list.Add(DataRowToModel(item));
                }
            }
            return list;
        }
        
        /// <summary>
        /// 通过存储过程添加一个用户
        /// </summary>
        /// <param name="username">用户账号</param>
        /// <param name="pwd">密码</param>
        /// <param name="maxusernum">最大允许创建子用户数</param>
        /// <param name="phone">电话号码</param>
        /// <param name="roleid">所属角色</param>
        /// <param name="parent">上级编号</param>
        /// <param name="nickname">昵称</param>
        /// <param name="Email">邮箱</param>
        /// <returns></returns>
        public int AddUserByProcedure(DeliveryManage.Model.Sys_User model)
        {
            SqlParameter[] parameters = {
                    new SqlParameter("@username", SqlDbType.NVarChar,50),
                    new SqlParameter("@userpwd", SqlDbType.NVarChar,50),
                    new SqlParameter("@maxuser", SqlDbType.Int,8),
                    new SqlParameter("@phone", SqlDbType.NVarChar,12),
                    new SqlParameter("@roleid", SqlDbType.Int,8),
                    new SqlParameter("@parent", SqlDbType.Int,8),
                    new SqlParameter("@nickname", SqlDbType.NVarChar,50),
                    new SqlParameter("@Email", SqlDbType.NVarChar,30),
                    new SqlParameter("@IsBasicSalary", SqlDbType.Bit),
                    new SqlParameter("@userid", SqlDbType.Int,8)
            };
            parameters[0].Value = model.Name;
            parameters[1].Value = model.Pwd;
            parameters[2].Value = model.MaxUserCount;
            parameters[3].Value = model.Telephone;
            parameters[4].Value = model.RoleType;
            parameters[5].Value = model.TopUser;
            parameters[6].Value = model.Nickname;
            parameters[7].Value = model.Email;
            parameters[8].Value = model.IsBasicSalary;
            parameters[9].Direction = ParameterDirection.Output;
            using (SqlDataReader da = DbHelperSQL.RunProcedure("PRO_USER_REGISTER", parameters))
            {
                
            }
            int userid = (int)parameters[9].Value;
            return userid;
        }

        public List<DeliveryManage.Model.Sys_User> GetTuiList(string strWhere)
        {
            List<DeliveryManage.Model.Sys_User> list = new List<DeliveryManage.Model.Sys_User>();
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * from (");
            strSql.Append("select * FROM Sys_User");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(") dual");
            DataSet ds = DbHelperSQL.Query(strSql.ToString());
            if (ds != null && ds.Tables[0] != null)
            {
                foreach (DataRow item in ds.Tables[0].Rows)
                {
                    list.Add(DataRowToModel(item));
                }
            }
            return list;
        }

        /// <summary>
        /// 修改一列数据
        /// </summary>
        public bool UpdateField(int id, string strValue)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update Sys_User set " + strValue);
            strSql.Append(" where id=" + id);
            return DbHelperSQL.ExecuteSql(strSql.ToString()) > 0;
        }

        /// <summary>
		/// 是否存在该记录
		/// </summary>
		public bool ExistsName(string username)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from Sys_User");
            strSql.Append(" where name=@name");
            SqlParameter[] parameters = {
                    new SqlParameter("@name", SqlDbType.NVarChar,30)
            };
            parameters[0].Value = username;

            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }

        /// <summary>
		/// 获取用户对应的代理ID
		/// </summary>
		public int GetAgent(int userid)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select userid from [dbo].[Sys_UserRelation] ");
            strSql.Append(" where childid =@userid and parentid=100000 ");
            SqlParameter[] parameters = {
                    new SqlParameter("@userid", SqlDbType.Int,10)
            };
            parameters[0].Value = userid;

            object val = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
            return Convert.ToInt32(val);
        }
        
        public List<string> GetChild(string id)
        {
            List<string> userids = new List<string>();
            DataSet ds = DbHelperSQL.Query("select Id from Sys_User where TopUser=" + id);
            foreach (DataRow item in ds.Tables[0].Rows)
            {
                string tid = item[0].ToString();
                userids.Add(tid);
                userids.AddRange(GetChild(tid));
            }
            return userids;
        }
        public void CreatUserTree(Model.Sys_User u, Dictionary<int, Model.Sys_User> UserList)
        {
            DataSet ds = this.GetListSMUser("TopUser=" + u.Id);
            u.ChildList = new List<Model.Sys_User>();
            if (ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow item in ds.Tables[0].Rows)
                {
                    Model.Sys_User Su = new Model.Sys_User();
                    Su.Id = Convert.ToInt32(item["Id"]);
                    Su.Name = Convert.ToString(item["Name"]);
                    Su.Pwd = Convert.ToString(item["Pwd"]);
                    Su.MaxUserCount = Convert.ToInt32(item["MaxUserCount"]);
                    Su.ChildCount = Convert.ToInt32(item["ChildCount"]);
                    Su.TopUser = Convert.ToInt32(item["TopUser"]);
                    Su.Status = Convert.ToBoolean(item["Status"]);
                    Su.LastTask = Convert.ToInt32(item["LastTask"]);
                    Su.Telephone = Convert.ToString(item["Telephone"]);
                    Su.Nickname = Convert.ToString(item["Nickname"]);
                    Su.RoleType = Convert.ToInt32(item["RoleType"]);
                    Su.Email = Convert.ToString(item["Email"]);
                    Su.RemandVal = Convert.ToInt32(item["RemandVal"]);
                    Su.IsDisabledCall = Convert.ToBoolean(item["IsDisabledCall"]);
                    Su.UnUploadCount = Convert.ToInt32(item["UnUploadCount"]);
                    Su.IsBasicSalary = Convert.ToBoolean(item["IsBasicSalary"]);
                    Su.NewOrderId = 0;
                    u.ChildList.Add(Su);
                    UserList.Add(Su.Id, Su);
                    if (Su.MaxUserCount > 0)
                    {
                        this.CreatUserTree(Su, UserList);
                    }
                }
            }
        }
        public List<Model.Sys_User> CreatUserTreeForSourseSituation(string where)
        {
            List<Model.Sys_User> list = new List<Model.Sys_User>();
            DataSet ds = this.GetListSMUser(where);

            if (ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow item in ds.Tables[0].Rows)
                {
                    Model.Sys_User Su = new Model.Sys_User();
                    Su.Id = Convert.ToInt32(item["Id"]);
                    Su.Name = Convert.ToString(item["Name"]);
                    Su.Nickname = item["Nickname"] + "";
                    Su.MaxUserCount = Convert.ToInt32(item["MaxUserCount"]);
                    Su.ChildCount = Convert.ToInt32(item["ChildCount"]);
                    list.Add(Su);
                }
            }
            return list;
        }

        public Model.Sys_User CreatUserTreeForSourseSituationForModel(string where)
        {
            Model.Sys_User model = new Model.Sys_User();
            DataSet ds = this.GetListSMUser(where);

            if (ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow item in ds.Tables[0].Rows)
                {
                    model.Id = Convert.ToInt32(item["Id"]);
                    model.Name = Convert.ToString(item["Name"]);
                    model.Nickname = item["Nickname"] + "";
                    model.MaxUserCount = Convert.ToInt32(item["MaxUserCount"]);
                    model.ChildCount = Convert.ToInt32(item["ChildCount"]);
                }
            }
            return model;
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetListSMUser(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select a.Id,a.Name,a.MaxUserCount,a.Pwd,a.ChildCount,a.TopUser,a.Status,a.LastTask,a.Nickname,a.Telephone,a.RoleType,a.Email,a.RemandVal,a.IsDisabledCall,a.UnUploadCount,a.IsBasicSalary ");
            strSql.Append(" FROM  Sys_User a ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }
       

        public List<Model.Sys_User> GetListResult(string sqlWhere)
        {
            DataSet ds = this.GetList(sqlWhere);
            List<Model.Sys_User> result = new List<Model.Sys_User>();
            if (ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow item in ds.Tables[0].Rows)
                {
                    Model.Sys_User model = new Model.Sys_User();
                    if (item["Id"].ToString() != "" && item["Id"].ToString() != "0")
                    {
                        model.Id = Convert.ToInt32(item["Id"]);
                    }
                    if (item["Name"].ToString() != "")
                    {
                        model.Name = item["Name"].ToString();
                    }
                    if (item["Pwd"].ToString() != "")
                    {
                        model.Pwd = item["Pwd"].ToString();
                    }
                    if (item["MaxUserCount"].ToString() != "")
                    {
                        model.MaxUserCount = Convert.ToInt32(item["MaxUserCount"]);
                    }
                    if (item["ChildCount"].ToString() != "")
                    {
                        model.ChildCount = Convert.ToInt32(item["ChildCount"]);
                    }
                    if (item["TopUser"].ToString() != "")
                    {
                        model.TopUser = Convert.ToInt32(item["TopUser"]);
                    }
                    if (item["Status"].ToString() != "")
                    {
                        model.Status = Convert.ToBoolean(item["Status"]);
                    }
                    if (item["LastTask"].ToString() != "")
                    {
                        model.LastTask = Convert.ToInt32(item["LastTask"]);
                    }
                    if (item["Nickname"].ToString() != "")
                    {
                        model.Nickname = item["Nickname"].ToString();
                    }
                    if (item["Telephone"].ToString() != "")
                    {
                        model.Telephone = item["Telephone"].ToString();
                    }
                    if (item["RoleType"].ToString() != "")
                    {
                        model.RoleType = Convert.ToInt32(item["RoleType"]);
                    }
                    if (item["Email"].ToString() != "")
                    {
                        model.Email = item["Email"].ToString();
                    }
                    if (item["RemandVal"].ToString() != "")
                    {
                        model.RemandVal = Convert.ToInt32(item["RemandVal"]);
                    }
                    if (item["IsDisabledCall"].ToString() != "")
                    {
                        model.IsDisabledCall = Convert.ToBoolean(item["IsDisabledCall"]);
                    }
                    if (item["UnUploadCount"].ToString() != "")
                    {
                        model.UnUploadCount = Convert.ToInt32(item["UnUploadCount"]);
                    }
                    if (item["IsBasicSalary"].ToString() != "")
                    {
                        model.IsBasicSalary = Convert.ToBoolean(item["IsBasicSalary"]);
                    }
                    
                    result.Add(model);
                }
            }
            return result;
        }
        
        /// <summary>
        /// 调组更新员工所属上级账户
        /// </summary>
        /// <param name="id"></param>
        /// <param name="groupId"></param>
        /// <returns></returns>
        public bool DepartmentUpdate(int id, int groupId)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" update Sys_User set ");
            strSql.Append(" TopUser = " + groupId + " ");
            strSql.Append(" where Id =" + id);
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString());
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        public List<Model.Sys_User> GetUserListResult(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" select Id,Name,Nickname from Sys_User where id in ( select userid from [dbo].[Sys_UserRelation] where childid=" + id + " and fxlevel = 1 ");
            strSql.Append(" union all ");
            strSql.Append(" select childid from Sys_UserRelation where userid = (select userid from [dbo].[Sys_UserRelation] ");
            strSql.Append(" where childid="+id+"  and fxlevel = 1) and fxlevel = 1) and id != "+id);
            
            DataSet ds = DbHelperSQL.Query(strSql.ToString());
            List<Model.Sys_User> result = new List<Model.Sys_User>();
            if (ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow item in ds.Tables[0].Rows)
                {
                    Model.Sys_User model = new Model.Sys_User();
                    if (item["Id"].ToString() != "" && item["Id"].ToString() != "0")
                    {
                        model.Id = Convert.ToInt32(item["Id"]);
                    }
                    if (item["Name"].ToString() != "")
                    {
                        model.Name = item["Name"].ToString();
                    }
                    if (item["Nickname"].ToString() != "")
                    {
                        model.Nickname = item["Nickname"].ToString();
                    }
                    result.Add(model);
                }
            }
            return result;
        }
        public List<Model.Sys_User> GetLowerUserListResult(int id,int sid,int RoleType)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" select Id,Name,Nickname from Sys_User where id in(select childid from [dbo].[Sys_UserRelation] where userid="+ id + " and fxlevel = 1) and id!="+ sid);
            //strSql.Append(" select Id,Name,Nickname from Sys_User where id in(select childid from [dbo].[Sys_UserRelation] where userid=" + id + " and fxlevel = 1) and id!=" + sid + "  and RoleType!=" + RoleType);

            DataSet ds = DbHelperSQL.Query(strSql.ToString());
            List<Model.Sys_User> result = new List<Model.Sys_User>();
            if (ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow item in ds.Tables[0].Rows)
                {
                    Model.Sys_User model = new Model.Sys_User();
                    if (item["Id"].ToString() != "" && item["Id"].ToString() != "0")
                    {
                        model.Id = Convert.ToInt32(item["Id"]);
                    }
                    if (item["Name"].ToString() != "")
                    {
                        model.Name = item["Name"].ToString();
                    }
                    if (item["Nickname"].ToString() != "")
                    {
                        model.Nickname = item["Nickname"].ToString();
                    }
                    result.Add(model);
                }
            }
            return result;
        }

        #endregion  ExtensionMethod
    }
}

